package com.zret.oasys.dao.impl;

import java.util.List;

import org.apache.tomcat.dbcp.dbcp2.Jdbc41Bridge;

import com.zret.oasys.dao.EmpDao;
import com.zret.oasys.dbtools.JDBCUtils;
import com.zret.oasys.dbtools.PageData;
import com.zret.oasys.entity.Emp;
import com.zret.oasys.entity.EmpDeptPosition;

public class EmpDaoImpl implements EmpDao {

	@Override
	// 遍历所有员工
	public List<Emp> getEmps() {
		String sql = "select * from emp";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class, null);
		return list;
	}

	@Override
	// 根据id删除员工
	public boolean deleteEmps(int empid) {
		String sql = "delete from emp where empid = ?";
		return JDBCUtils.exUpdate(sql, empid) > 0;
	}

	public boolean addEmps(Emp emp) {
		// 连接数据库
		String sql = "insert into emp values (null,?,?,?,?,?,?,?,?,?)";
		int n = JDBCUtils.exUpdate(sql, emp.getPosition(), emp.getDeptid(), emp.getEmpname(), emp.getSex(),
				emp.getHiredate(), emp.getSalary(), emp.getUsername(), emp.getPassword(), emp.getFaceflag());
		return n > 0;
	}

	@Override
	// 通过ID查找员工
	public Emp getEmpsById(int empid) {
		String sql = "select * from emp where empid = ? ";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class, empid);
		return list.get(0);
	}

	@Override
	// 通过名字模糊查找员工
	public List<Emp> getEmpsByName(String empname) {
		String sql = "select * from emp where empname like ?";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class, "%" + empname + "%");
		return list;
	}

	@Override
	// 通过入职时间区间查找员工
	public List<Emp> getEmpsByTime(String startTime, String endTime) {
		// TODO Auto-generated method stub
		String sql = "select * from emp where hiredate between ? and ? ";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class, startTime, endTime);
		return list;
	}

	@Override
	// 更改员工信息
	public boolean updateEmp(Emp emp) {
		// TODO Auto-generated method stub
		String sql = "update emp set position=?, deptid=?, empname=?, sex=?, hiredate=?, salary=?, username=?,faceflag=? where empid=?";
		int n = JDBCUtils.exUpdate(sql, emp.getPosition(), emp.getDeptid(), emp.getEmpname(), emp.getSex(),
				emp.getHiredate(), emp.getSalary(), emp.getUsername(), emp.getFaceflag(),
				emp.getEmpid());
		return n > 0;
	}

	@Override
	//通过username查找用户
	public Emp getEmpsByUsername(String username) {
		String sql = "select * from emp where username = ? ";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class, username);
		if (list.size()==0) {
			return null;
		}else {
			return list.get(0);
		}
	}

	@Override
	//所有员工的分页查询
	public PageData<Emp> getEmpsByPage(int page, int pageSize) {
		String sql = "select * from emp";
		PageData<Emp> pd =JDBCUtils.exQueryByPage(sql, Emp.class, page, pageSize, null);
		return pd;
	}
	//分页模糊查询员工
	@Override
	public PageData<Emp> getEmpsByPage(int page, int pageSize, String empname) {
		String sql = "select * from emp where empname like ?";
		PageData<Emp> pd = JDBCUtils.exQueryByPage(sql, Emp.class, page, pageSize, "%"+empname+"%");
		return pd;
	}
	//登录查询操作
	@Override
	public List<Emp> GetLogin(String username, String password) {
		String sql = "select * from emp where username=?and password=?";
		List<Emp> list=JDBCUtils.exQuery(sql, Emp.class, username,password);
		return list;
	}
	
	/**
	 * 更改用户密码
	 */
	@Override
	public boolean changePwd(String username, String newpwd) {
		String sql = "update emp set password = ? where username = ?";
		int n = JDBCUtils.exUpdate(sql, newpwd, username);
		return n > 0;
	}
   /**
    * 分页多表查询
    */
	@Override
	public PageData<EmpDeptPosition> getEmpDeptPositionByPage(int page,int pageSize,String key) {
		 String sql="SELECT emp.empid,position.positionname,dept.deptname,emp.empname,emp.sex,emp.hiredate,emp.salary,emp.username,emp.`password`,emp.faceflag FROM position,emp,dept WHERE emp.deptid=dept.deptid and position.position=emp.position and empname like ?";
		PageData<EmpDeptPosition> pd =JDBCUtils.exQueryByPage(sql, EmpDeptPosition.class, page, pageSize, "%"+key+"%");
		return pd;
	}

@Override
public List<Emp> getEmpManager(int empid) {
	String sql="SELECT * FROM emp WHERE empid=(SELECT de.managerid FROM emp AS em INNER JOIN dept AS de ON em.deptid=de.deptid WHERE em.empid=?)";
	  List<Emp> list=JDBCUtils.exQuery(sql, Emp.class, empid);
	return list;
}
    
}
